package kr.lgh.sms_creditcard.DB;

import java.lang.reflect.Array;
import java.util.ArrayList;
import java.util.List;

import kr.lgh.sms_creditcard.BankType.BankInfo;
import kr.lgh.sms_creditcard.Setting.CategoryInfo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SQLiteDBHelper extends SQLiteOpenHelper {

	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "smsDB";
	private static final String CARD_TABLE = "Card_Table";
	private static final String CATEGORY_TABLE = "Category_Table";
	private static final String MESSAGE_TABLE = "Message_Table";
	final String[] CARDNUMBER = { "15884000", "15665050", "15881788" };
	final String[] CARDLIST = { "대구BC", "DGB", "KB국민체크" };
	final String[] CATEGORY_LIST = {"없음", "식비", "문화생활비", "건강관리비", "교통비", "학비",
			"사회생활비", "보험비", "유흥비", "경조사비", "기타" };

	public SQLiteDBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.d("TAG", "SQLiteDBHelper - onCreate()");
		ContentValues values = null;

		// Card_Table Create
		String card_sql = "create table if not exists " + CARD_TABLE
				+ "(_id integer primary key autoincrement," + "_number text, "
				+ "_card text, " + "_check boolean" + ");";

		db.execSQL(card_sql);

		for (int i = 0; i < CARDLIST.length; i++) {
			values = new ContentValues();
			values.put("_number", CARDNUMBER[i]);
			values.put("_card", CARDLIST[i]);
			values.put("_check", false);

			db.insert(CARD_TABLE, null, values);
		}

		values.clear();

		// Category_Table Create
		String category_sql = "create table if not exists " + CATEGORY_TABLE
				+ "(_id integer primary key autoincrement," + "_name text"
				+ ");";

		db.execSQL(category_sql);

		for (int i = 0; i < CATEGORY_LIST.length; i++) {
			values = new ContentValues();
			values.put("_name", CATEGORY_LIST[i]);
			Log.d("TAG", "category " + i + "번쨰 추가 : " + CATEGORY_LIST[i]);

			db.insert(CATEGORY_TABLE, null, values);
		}
		
		// Message_Table Create
		String message_sql = "create table if not exists " + MESSAGE_TABLE
				+ "(_id integer primary key autoincrement," + "_year int, "
				+ "_month int, "
				+ "_date text, "
				+ "_time text, "
				+ "_money int, "
				+ "_card text, "
				+ "_place text, "
				+ "_category text, "
				+ "_cardT_id int, "
				+ "_categoryT_id int"
				+ ");";

		db.execSQL(message_sql);

	}
	
	// 카테고리, 날짜별 정보가져오기 (Message_Table)
	public ArrayList<BankInfo> categoryToList(String category, int year,
			int month) {

		Log.d("TAG", "categoryToList() year : " + year + " / month : " + month
				+ " / category : " + category);
		ArrayList<BankInfo> categoryToList = new ArrayList<BankInfo>();
		String sql = "select _date, _time, _money, _place, _card from "
				+ MESSAGE_TABLE + " where (_category = '" + category
				+ "' AND _year = " + year + " AND _month = " + month
				+ ") order by _date desc, _time desc" + ";";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery(sql, null);

		if (cur.moveToFirst()) {
			do {
				BankInfo info = new BankInfo();
				info.setS_date(cur.getString(0));
				info.setS_time(cur.getString(1));
				info.setS_money(cur.getInt(2));
				info.setS_place(cur.getString(3));
				info.setS_card(cur.getString(4));

				categoryToList.add(info);
			} while (cur.moveToNext());
		}
		return categoryToList;
	}
	
	// 카테고리 정보를 수정 (Message_Table)
	public void update_Category(String category, int category_id, String date, String time){
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "update " + MESSAGE_TABLE + " set _category = '" + category + "', _categoryT_id = " + category_id + " where _date = '" + date + "' and _time = '" + time + "';";
		db.execSQL(sql);
		db.close();
	}
	
	// 카테고리 _id정보 가져오기 (Message_Table)
	public int findToCategoryT_id(String date, String time){
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery("select _categoryT_id from " + MESSAGE_TABLE + " where _date = '" + date + "' and _time = '" + time + "';", null);
		cursor.moveToFirst();
		
		int categoryT_id = cursor.getInt(0);
		cursor.close();
		db.close();
		
		return categoryT_id;
	}
	
	// 원하는 카테고리 정보 가져오기 (Message_Table)
	public String findToCategory(String date, String time){
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery("select _category from " + MESSAGE_TABLE + " where _date = '" + date + "' and _time = '" + time + "';", null);
		cursor.moveToFirst();
		
		String category = cursor.getString(0);
		cursor.close();
		db.close();
		
		return category;
	}
	
	// CardToList (카드별 월별 데이터 받아오기)
	public ArrayList<BankInfo> cardToList(String card, int year, int month){
	 		
			Log.d("TAG", "cardToList() month : " + month + " / year : " + year + " / card : " + card);
			ArrayList<BankInfo> cardToList = new ArrayList<BankInfo>();
		String sql = "select _date, _time, _money, _place, _card from "
				+ MESSAGE_TABLE + " where (_card = '" + card + "' AND _year = "
				+ year + " AND _month = " + month + ") order by _date desc, _time desc" +";";

			SQLiteDatabase db = this.getReadableDatabase();
			Cursor cur = db.rawQuery(sql, null);
			
			if (cur.moveToFirst()) {
				do {
					BankInfo info = new BankInfo();
					info.setS_date(cur.getString(0));
					info.setS_time(cur.getString(1));
					info.setS_money(cur.getInt(2));
					info.setS_place(cur.getString(3));
					info.setS_card(cur.getString(4));
					
					cardToList.add(info);
				} while (cur.moveToNext());
			}
			
			return cardToList;
	 		
	 	}
	
	// 월별 날짜, 시간, 금액, 장소 가져오기 (Message_Table), 다중행 서브쿼리 IN 연산자 사용
	public ArrayList<BankInfo> monthToList(int year, int month) {

		ArrayList<BankInfo> monthList = new ArrayList<BankInfo>();
		String sql = "select _date, _time, _money, _place, _card from "
				+ MESSAGE_TABLE + " where (_year = " + year
				+ " AND _month = " + month + " AND _cardT_id IN (select _id from Card_Table where _check = 'true'))"
				+ " order by _date desc, _time desc"
				+ ";";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery(sql, null);

		if (cur.moveToFirst()) {
			do {
				BankInfo kb = new BankInfo();
				kb.setS_date(cur.getString(0));
				kb.setS_time(cur.getString(1));
				kb.setS_money(cur.getInt(2));
				kb.setS_place(cur.getString(3));
				kb.setS_card(cur.getString(4));
				
				monthList.add(kb);
			} while (cur.moveToNext());
		}
		return monthList;
	}
	
	// Message_Table 테이블 데이터 반환 (Message_Table)
	public List<BankInfo> readAllData(){
		List<BankInfo> kb_list = new ArrayList<BankInfo>();
		String selectQuery = "select * from " + MESSAGE_TABLE;
		
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery(selectQuery, null);
		
		if(cur.moveToFirst()){
			do{
				BankInfo kb = new BankInfo();
				kb.setS_id(cur.getInt(0));
				kb.setS_year(cur.getInt(1));
				kb.setS_month(cur.getInt(2));
				kb.setS_date(cur.getString(3));
				kb.setS_time(cur.getString(4));
				kb.setS_money(cur.getInt(5));
				kb.setS_card(cur.getString(6));
				kb.setS_place(cur.getString(7));
				kb.setS_category(cur.getString(8));
				kb.setS_cardT_id(cur.getInt(9));
				kb.setS_categoryT_id(cur.getInt(10));
				
				kb_list.add(kb);
			} while(cur.moveToNext());
		}
		return kb_list;
	}
	
	// Message_Table 테이블 추가 (Message_Table)
	public void insert_MsgData(BankInfo bankinfo, String formattedDate, String formattedCard){
		SQLiteDatabase db = this.getWritableDatabase();
		Log.d("TAG", "getS_date : " + bankinfo.getS_date());
		String sql = "insert into " + MESSAGE_TABLE 
				+ "(_year, _month, _date, _time, _money, _card, _place, _category, _cardT_id, _categoryT_id)"
				+ " select "
				+ bankinfo.getS_year() + ", " 
				+ bankinfo.getS_month() + ", "
				+ "'" + bankinfo.getS_date() + "', "				
				+ "'" + bankinfo.getS_time() + "', "
				+ "'" + bankinfo.getS_money() + "', "
				+ "'" + bankinfo.getS_card() + "', "
				+ "'" + bankinfo.getS_place() + "', "
				+ "'" + bankinfo.getS_category() + "', "
				+ bankinfo.getS_cardT_id() + ", "
				+ bankinfo.getS_categoryT_id()
				+ " where not exists("
				+ "select 1 from " + MESSAGE_TABLE
				+ " where _card = '"
				+ bankinfo.getS_card()
				+ "' and _date = '"
				+ bankinfo.getS_date()
				+ "' and _time = '"
				+ bankinfo.getS_time()
				+ "');";
		db.execSQL(sql);
		db.close();
	}

	// 카테고리 항목 수정 (Category_Table)
	public void modify_CategoryData(String name, int id) {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "update " + CATEGORY_TABLE + " set _name = '" + name
				+ "' where _id = " + id + ";";

		db.execSQL(sql);
		db.close();
	}

	// 카테고리 항목 삭제 (Category_Table)
	public void delete_CategoryData(int id) {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "delete from " + CATEGORY_TABLE + " where _id = " + id
				+ ";";

		db.execSQL(sql);
		db.close();
	}

	// 카테고리 추가 (Category_Table)
	public void insert_CategoryData(CategoryInfo info) {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "insert into " + CATEGORY_TABLE + " values(NULL, '"
				+ info.getName() + "');";
		db.execSQL(sql);
		db.close();
	}

	// 체크상태 반환 (Card_Table)
	public boolean[] select_CheckState(int size) {
		boolean[] arCheck = new boolean[size];
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery("select _check from " + CARD_TABLE, null);
		cursor.moveToFirst();
		int num = 0;

		while (!cursor.isAfterLast()) {
			if (cursor.getString(0).contains("false")) {
				Log.d("TAG", "arCheck : " + arCheck[num]);
				arCheck[num] = false;
			} else {
				Log.d("TAG", "arCheck : " + arCheck[num]);
				arCheck[num] = true;
			}
			num++;

			cursor.moveToNext();
		}
		cursor.close();
		db.close();

		return arCheck;

	}

	// 체크된 카드만 반환 (Card_Table)
	public ArrayList<String> checkCardList(){
		SQLiteDatabase db = this.getWritableDatabase();
		String sql;
		ArrayList<String> checkList = new ArrayList<String>();
		
		Cursor cursor = db.rawQuery("select _card from " + CARD_TABLE + " where _check = 'true'" , null);
		cursor.moveToFirst();

		while (!cursor.isAfterLast()) {
			checkList.add(cursor.getString(0));
			cursor.moveToNext();
		}
		cursor.close();
		db.close();
		
		return checkList;
		
	}
	
	// 체크 상태 지정 (Card_Table)
	public void setCheck_State(boolean[] mSelect) {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql;

		for (int i = 0; i < mSelect.length; i++) {
			sql = "update " + CARD_TABLE + " set _check = '" + mSelect[i]
					+ "' where _id = '" + (i + 1) + "';";
			db.execSQL(sql);
		}
		db.close();
	}
	
	// 카드번호 리스트 (Card_Table)
	public ArrayList<String> select_CardNumberList(){
		ArrayList<String> arNumber = new ArrayList<String>();
		SQLiteDatabase db = this.getReadableDatabase();
		
		Cursor cursor = db.rawQuery("select _number from " + CARD_TABLE, null);
		cursor.moveToFirst();

		while (!cursor.isAfterLast()) {
			arNumber.add(cursor.getString(0));
			cursor.moveToNext();
		}
		cursor.close();
		db.close();
		
		return arNumber;
	}
	
	// 카드이름에 대한 ID값 반환 (Card_Table)
	public int select_CardToID(String _card){
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery("select _id from " + CARD_TABLE + " where _card = '" + _card + "';", null);
		cursor.moveToFirst();
		
		int id = cursor.getInt(0);
		cursor.close();
		db.close();
		
		return id;
	}

	// 카드이름 리스트 (Card_Table)
	public ArrayList<String> select_CardList() {
		ArrayList<String> arCard = new ArrayList<String>();
		SQLiteDatabase db = this.getReadableDatabase();
		int num = 0;
		Cursor cursor = db.rawQuery("select _card from " + CARD_TABLE, null);
		cursor.moveToFirst();

		while (!cursor.isAfterLast()) {
			arCard.add(cursor.getString(0));
			num++;
			cursor.moveToNext();
		}
		cursor.close();
		db.close();

		return arCard;
	}

	// 카드 테이블 초기화 (Card_Table)
	public void initial_CardTable() {
		Log.d("TAG", "initial_CardTable()");
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values;

		for (int i = 0; i < CARDLIST.length; i++) {
			values = new ContentValues();
			values.put("_number", CARDNUMBER[i]);
			values.put("_card", CARDLIST[i]);
			values.put("_check", false);

			db.insert(CARD_TABLE, null, values);
		}
		db.close();
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

}
